# coding=utf-8
"""
@author: hyq
@time: 2019/7/23 16:36
"""
from sqlalchemy import create_engine, ForeignKey
from sqlalchemy.orm import sessionmaker, relationships
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, DateTime, Boolean
from datetime import datetime

# 创建数据库连接
mysql_url = "mysql+pymysql://root:123456@localhost:3306/guest?charset=utf8"
engine = create_engine(mysql_url, echo=True)
# print(dir(engine))
# 生成orm基类，执行SQL语句的类就继承Base
Base = declarative_base()
# 将创建好的user类，映射到数据库的user表中
Base.metadata.create_all(engine)
# 创建会话
Session = sessionmaker(bind=engine)  # 创建用于数据库session的类


# 创建表对应的类
class Sign_Event(Base):
    __tablename__ = 'sign_event'
    id = Column(Integer, primary_key=True)
    name = Column(String(100), nullable=False)
    limit = Column(Integer)
    status = Column(Boolean)
    address = Column(String(200))
    start_time = Column(DateTime)
    create_time = Column(DateTime, default=datetime.now(), comment='创建时间')

    def __repr__(self):  # 如果想让它变的可读，只需在定义表的类下面加上这样的代码
        return '<id="%d",name="%s",limit="%d,status="%s,address="%s,start_time="%s,create_time="%s>' % (
            self.id, self.name, self.limit, self.status, self.address, self.start_time, self.create_time)


class Sign_Guest(Base):
    __tablename__ = 'sign_guest'
    id = Column(Integer, primary_key=True)
    event_id = Column(Integer)
    realname = Column(String(64), nullable=False, comment='姓名')
    phone = Column(String(16), nullable=False, comment='手机号')
    email = Column(String(50), nullable=False, comment='邮箱')
    sign = Column(Boolean, default=False, comment='签到状态')
    create_time = Column(DateTime, default=datetime.now(), comment='创建时间')

    def __repr__(self):  # 如果想让它变的可读，只需在定义表的类下面加上这样的代码
        return '<id="%d",event_id="%d",realname="%s,phone="%s,email="%s,sign="%s,create_time="%s>' % (
            self.id, self.event_id, self.realname, self.phone, self.email, self.sign, self.create_time)


class DB():
    def __init__(self):
        self.session = Session()  # 这里才是生成session实例可以理解为cursor

    def add_data(self):
        '''添加数据'''
        event1 = Sign_Event(id=1, name='red mi', limit=100, status=True, address='奥森',
                                    start_time=datetime.strptime('2019-8-10 10:00:00', '%Y-%m-%d %H:%M:%S'))
        guest1 = Sign_Guest(id=1, event_id=1, realname='鱼鱼', phone='1351111111', email='123@163.com')
        try:
            self.session.add(event1)
            self.session.add(guest1)
            self.session.commit()
        except:
            self.session.rollback()
        finally:
            self.session.close()


    def delete_data(self):
        '''删除数据'''
        try:
            self.session.query(Sign_Guest).delete()
            self.session.query(Sign_Event).delete()
            self.session.commit()
        except:
            self.session.rollback()
        finally:
            self.session.close()



    def select_data_one(self,tableName,colname,param):
        '''查询单条数据'''
        self.session.query(tableName).filter(colname==param)


    def select_data_more(self):
        '''查询多条数据'''
        pass

    def update_data(self):
        '''修改数据'''
        pass


if __name__=='__main__':
    db = DB()
    db.delete_data()
    # db.add_data()